create database Student
on
(
	name='Student_data',
	filename='F:\sql\Student_data.mdf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='F:\sql\Student_log.ldf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Student
go
create table Class
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(20) unique not null 
)

insert into Class (ClassName)
select 'class1' union
select 'class2' union
select 'class3' union
select 'class4' union
select 'class5' union
select 'class6' union
select 'class7' union
select 'class8' union
select 'class9' union
select 'class10'union
select 'class11' union
select 'class12' union
select 'class13' union
select 'class14' union
select 'class15' union
select 'class16' union
select 'class17' union
select 'class18' union
select 'class19' union
select 'class20'
select * from Class

update Class set ClassName='classone' where ClassID=1
delete from Class where ClassID=9

create table Student
(
	StuID int primary key identity(1,1),
	ClassID int  references Class(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) default('男') check(StuSex='男' or StuSex='女'),
	StuBirthday date ,
	StuPhone nvarchar(11) unique not null,
	StuAddress nvarchar(200),
	CreateDate datetime default (getdate())
)

insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('1','aa','男','2001-09-23','13467838903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('2','aa','男','2001-09-23','13467848903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('3','aa','男','2001-09-23','13465858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('4','aa','男','2001-09-23','13457858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('5','aa','男','2001-09-23','13667858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('6','aa','男','2001-09-23','13367858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('7','aa','男','2001-09-23','13767858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('8','aa','男','2001-09-23','13467658903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('9','aa','男','2001-09-23','13467858703','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('10','aa','男','2001-09-23','13467858904','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('11','aa','男','2001-09-23','11467838903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('12','aa','男','2001-09-23','12467848903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('13','aa','男','2001-09-23','13465858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('14','aa','男','2001-09-23','14457858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('15','aa','男','2001-09-23','15667858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('16','aa','男','2001-09-23','16367858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('17','aa','男','2001-09-23','17767858903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('18','aa','男','2001-09-23','18467658903','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('19','aa','男','2001-09-23','19467858703','....')
insert into Student(ClassID,StuName,StuSex,StuBirthday,StuPhone,StuAddress)
values('20','aa','男','2001-09-23','31467858904','....')
select * from Student

alter table Student add CreateDate datetime

update Student set CreateDate= DEFAULT (getdate()) where ClassID=1
update Student set CreateDate= DEFAULT (getdate()) where ClassID=2
update Student set CreateDate= DEFAULT (getdate()) where ClassID=3
update Student set CreateDate= DEFAULT (getdate()) where ClassID=4
update Student set CreateDate= DEFAULT (getdate()) where ClassID=5
update Student set CreateDate= DEFAULT (getdate()) where ClassID=6
update Student set CreateDate= DEFAULT (getdate()) where ClassID=7
update Student set CreateDate= DEFAULT (getdate()) where ClassID=8
update Student set CreateDate= DEFAULT (getdate()) where ClassID=9
update Student set CreateDate= DEFAULT (getdate()) where ClassID=10
update Student set CreateDate= DEFAULT (getdate()) where ClassID=11
update Student set CreateDate= DEFAULT (getdate()) where ClassID=12
update Student set CreateDate= DEFAULT (getdate()) where ClassID=13
update Student set CreateDate= DEFAULT (getdate()) where ClassID=14
update Student set CreateDate= DEFAULT (getdate()) where ClassID=15
update Student set CreateDate= DEFAULT (getdate()) where ClassID=16
update Student set CreateDate= DEFAULT (getdate()) where ClassID=17
update Student set CreateDate= DEFAULT (getdate()) where ClassID=18
update Student set CreateDate= DEFAULT (getdate()) where ClassID=19
update Student set CreateDate= DEFAULT (getdate()) where ClassID=20

delete from Class where ClassID=9


create table Course
(
	CourseID int primary key identity,
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) check(CourseCredit>=1 and CourseCredit<=5) not null,
	CourseCredits nvarchar(10)  check(CourseCredits='专业课' or CourseCredits='公开课')
)

insert into Course(CourseName) values ('语文')
insert into Course(CourseName) values ('数学')
insert into Course(CourseName) values ('英语')
insert into Course(CourseName) values ('体育')
insert into Course(CourseName) values ('毛概')
insert into Course(CourseName) values ('SQL')
SELECT * from Course

update Course set CourseCredit=4 where CourseName ='SQL'

create table Score
(
	ScoreID int  primary key identity not null,
	StuID int references Student(StuID),
	CourseID int references Course(CourseId),
	Score decimal(5,2) unique not null
)

insert into Score (Score)
select '95' union
select '95' union
select '95' union
select '95' union
select '95' union
select '95' union
select '92' union
select '93' union
select '94' union
select '95' union
select '92' union
select '93' union
select '92' union
select '93' union
select '92' union
select '93' union
select '94' union
select '94' union
select '94' union
select '95'

select * from Score

delete from Score where StuID=1
delete from Score where CourseId=1
alter table Score add constraint CK_Score_Score check(Score>=0 and Score<=100)
